


ALTER                     proc sp_get_assembly_build
  @product_id int,
  @processstep_id int,
  @vendor_id varchar(100),
  @assembly_releaseId int,
  @location_id int
  
  AS
  BEGIN
  
  declare @temp_process_type_name varchar(50)
  declare @temp_product_name varchar(50)
  declare @temp_vendor_name varchar(50)
  declare @temp_city varchar(50)
  declare @temp_address_id int
  declare @temp_address_id1 int
  declare @temp_vendor_name1 varchar(100)
  declare @temp_address_1 varchar(50)
  declare @temp_address_2 varchar(50)
  declare @temp_address_3 varchar(50)
  declare @temp_address_4 varchar(50)
  declare @temp_city1 varchar(50)
  declare @temp_state varchar(50)
  declare @temp_zip varchar(50)
  declare @temp_country varchar(50)
  declare @temp_package varchar(200)
  declare @temp_fuse_file_location varchar(50)
  declare @temp_wafer_map_location varchar(50)
  declare @temp_line_1 varchar(50)
  declare @temp_line_2 varchar(50)
  declare @temp_line_3 varchar(50)
  declare @temp_line_4 varchar(50)
  declare @temp_line_5 varchar(50)
  declare @temp_line_6 varchar(50)
  declare @temp_mark_at_assembly varchar(50)
  declare @temp_mark_at_test varchar(50)
 
  declare @temp_po_number varchar(50)
  declare @temp_release_date varchar(50)
  declare @temp_bonding_diagram varchar(50)
  declare @temp_pn_marking varchar(50)
  declare @temp_category varchar(50)
  declare @temp_build_instruction varchar(50)
  declare @temp_special_instruction varchar(2000)
  declare @temp_status_id int
  declare @temp_assembly_status varchar(20)

  /*declare @temp_fnProcessStep_id bigint
  declare @temp_vendor_id varchar(10)
  declare @temp_from_location_id varchar(2000)
  declare @temp_from_location_id1 varchar(2000)
  declare @temp_lot_id bigint
  declare @idx INT*/
 
  declare @sql varchar(2000)
  
  declare @results Table (Process_Type_Name varchar(50), Product_Name varchar(50), Ship_From_Location varchar(50), Vendor_Name varchar(100), Address_1 varchar(50), Address_2 varchar(50), Address_3 varchar(50), Address_4 varchar(50), City varchar(50), State varchar(50), Zip varchar(50), Country varchar(50), Package varchar(200), Fuse_File_Location varchar(50), Wafer_Map_Location varchar(50), Line_1 varchar(50), Line_2 varchar(50), Line_3 varchar(50), Line_4 varchar(50), Line_5 varchar(50), Line_6 varchar(50), Mark_At_Assembly varchar(50), Mark_At_Test varchar(50), Po_Number varchar(50), Release_Date varchar(50), Bonding_Diagram varchar(50), Pn_Marking varchar(50), Category varchar(50), Build_Instruction varchar(50), Special_Instructions varchar(2000), Assembly_Release_Status varchar(20) )
  
  select @temp_process_type_name = part_number from product where processstep_id = @processstep_id 
  select @temp_product_name = part_number from product where product_id = @product_id
  select @temp_address_id1 = address_id, @temp_vendor_name1 = L_Vendor_Name from Location where location_id = @location_id
  select @temp_address_1 = Address_1, @temp_address_2 = Address_2, @temp_address_3 = Address_3, @temp_address_4 = Address_4, @temp_city1 = City, @temp_state = State, @temp_zip = Zipcode, @temp_country = Country from Address where address_id = @temp_address_id1
  select @temp_package = Parameter_Value from process_parameter where processstep_id = @processstep_id and parameter_key like 'package%' 
  select @temp_fuse_file_location = Fuse_File_Location, @temp_wafer_map_location = Wafer_Map_Location, @temp_line_1 = line_1, @temp_line_2 = line_2, @temp_line_3 = line_3, @temp_line_4 = line_4, @temp_line_5 =line_5, @temp_line_6 = line_6, @temp_mark_at_assembly = Mark_At_Assembly, @temp_mark_at_test = Mark_At_Test, 
  @temp_po_number = PO_Number, @temp_release_date = convert( varchar, Release_Date, 101), @temp_bonding_diagram = Bonding_Diagram, @temp_pn_marking = PN_Marking, @temp_category = Category, @temp_build_instruction = Build_Instruction, @temp_special_instruction = Special_Instructions, @temp_status_id = status_id
  from Assembly_Release_Table where AssemblyRelease_ID=@assembly_releaseId
  select @temp_assembly_status = assembly_release_status from status_table where status_id = @temp_status_id

  /*SELECT @temp_fnProcessStep_id = ProcessStep_ID from fn_get_process_flow_by_id(@processstep_id)
  SET @idx = 1
  WHILE ( LEN( @vendor_id ) > 0  )
  BEGIN
	SET @idx = CHARINDEX( ',', @vendor_id, @idx )
	IF ( @idx > 0 ) 
	BEGIN
		SET @temp_vendor_id   = SUBSTRING ( @vendor_id, 1, @idx-1 )
	    	SET @vendor_id = SUBSTRING ( @vendor_id, @idx+1, LEN ( @vendor_id) )
	END
	ELSE
	BEGIN
		SET @temp_vendor_id   = SUBSTRING ( @vendor_id, 1, LEN( @vendor_id) )
	    	SET @vendor_id = ''
	END
	SET @idx = 0
	SELECT @temp_lot_id = Lot_id from lot_table, Location, Address where lot_current_status <> 'Close' and processstep_id = @temp_fnProcessStep_id and lot_table.location_id = Location.location_id and Location.address_id = address.address_id and Location.location_id in ( select location_id from location where vendor_id = CONVERT(INT,@temp_vendor_id) )
	if @@ROWCOUNT <> 0
	BEGIN
		SET @temp_from_location_id1 = ''
		SET @temp_from_location_id1 = @temp_vendor_id 
		SET @temp_from_location_id  = ISNULL(@temp_from_location_id,'') + @temp_from_location_id1 + ','
	END
  END
  SET @temp_from_location_id = SUBSTRING ( @temp_from_location_id, 1 , LEN ( @temp_from_location_id)-1 ) 
  */  

  /*set @sql = N'Declare temp_cursor CURSOR FOR select DISTINCT address_id, L_Vendor_Name from location where vendor_id in( ' + @temp_from_location_id + ') and L_Vendor_Name IS NOT NULL'
  exec ( @sql )
  open temp_cursor fetch next from temp_cursor into @temp_address_id, @temp_vendor_name 
  while @@fetch_status = 0 
  begin
	select @temp_city=city from address where address_id = @temp_address_id  	 
	insert into @results 
	values ( @temp_process_type_name, @temp_product_name, @temp_vendor_name +'/'+ @temp_city, @temp_vendor_name1, @temp_address_1, @temp_address_2, @temp_address_3, @temp_address_4, @temp_city1, @temp_state, @temp_zip, @temp_country, @temp_package, @temp_fuse_file_location, @temp_wafer_map_location, @temp_line_1, @temp_line_2, @temp_line_3, @temp_line_4, @temp_line_5, @temp_line_6, @temp_mark_at_assembly, @temp_mark_at_test, @temp_po_number, @temp_release_date, @temp_bonding_diagram, @temp_pn_marking, @temp_category, @temp_build_instruction, @temp_special_instruction, @temp_assembly_status )
 
  fetch next from temp_cursor into @temp_address_id, @temp_vendor_name
  end
  close temp_cursor
  deallocate temp_cursor*/

  set @sql = N'Declare temp_cursor CURSOR FOR select DISTINCT Location.L_Vendor_Name + ' + '''/''' + ' + Address.City from Assembly_Release_Lot_Table, lot_table, location, Address  where Assembly_Release_Lot_Table.lot_id = lot_table.lot_id AND lot_table.location_id = location.location_id AND location.address_id = Address.address_id AND assemblyrelease_id = '+ CONVERT(VARCHAR,@assembly_releaseId) +' and lot_current_status <> ' + '''Close''' +  ''
  exec ( @sql )
  open temp_cursor fetch next from temp_cursor into @temp_vendor_name 
  while @@fetch_status = 0 
  begin
	insert into @results 
	values ( @temp_process_type_name, @temp_product_name, @temp_vendor_name, @temp_vendor_name1, @temp_address_1, @temp_address_2, @temp_address_3, @temp_address_4, @temp_city1, @temp_state, @temp_zip, @temp_country, @temp_package, @temp_fuse_file_location, @temp_wafer_map_location, @temp_line_1, @temp_line_2, @temp_line_3, @temp_line_4, @temp_line_5, @temp_line_6, @temp_mark_at_assembly, @temp_mark_at_test, @temp_po_number, @temp_release_date, @temp_bonding_diagram, @temp_pn_marking, @temp_category, @temp_build_instruction, @temp_special_instruction, @temp_assembly_status )
 
  fetch next from temp_cursor into @temp_vendor_name
  end
  close temp_cursor
  deallocate temp_cursor
  
  select * from @results
  
  end
 


